package com.tcfvic.server;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.tcfvic.client.PersonDetailsListService;
import com.tcfvic.client.panels.widgets.addons.advancedtable.DataFilter;
import com.tcfvic.client.panels.widgets.addons.advancedtable.TableColumn;
import com.tcfvic.server.persistance.HibernateUtil;

public class PersonDetailsListServiceImpl extends RemoteServiceServlet
		implements PersonDetailsListService {

	private static final long serialVersionUID = 2574119698402240940L;

	@Override
	public TableColumn[] getColumns() {
		TableColumn[] tc = new TableColumn[5];
		try {
			tc[0] = new TableColumn(column0Name, "id");
			tc[1] = new TableColumn(column2Name, "Surname");
			tc[2] = new TableColumn(column1Name, "First name");
			tc[3] = new TableColumn(column3Name, "Company");
			tc[4] = new TableColumn(column4Name, "Site");
		} catch (Exception e) {
			System.err.println(e);
		}
		return tc;
	}

	@SuppressWarnings("unchecked")
	@Override
	public String[][] getRows(int startRow, int rowsCount,
			DataFilter[] filters, String sortColumn, boolean sortOrder) {

		String[][] data = null;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			String hql = "select p.id, p.surname, p.firstname, wc.name, wc.site from "
					+ " Person p left outer join employmentdetails ed on p.id = ed.person_id "
					+ " left outer join workercompany wc on ed.workercompany_id = wc.id "
					+ " where (p.display is null or p.display like '') ";
			StringBuffer where = new StringBuffer();
			if (filters != null) {
				for (DataFilter df : filters) {
					if (df != null & !df.getValue().isEmpty()) {
						where.append(" and ");
						where.append(df.getColumn());
						where.append(" like '%");
						where.append(df.getValue() + "%'");
						// crit.add(Restrictions.like(df.getColumn(), df
						// .getValue()
						// + "%"));
					}
				}
			}
			// where.append("ORDER BY " + sortColumn + " "
			// + (sortOrder ? "ASC" : "DESC"));
			where.append("order by p.surname, p.firstname");
			Query query = session.createSQLQuery(hql + where);
			query.setFirstResult(startRow);
			query.setMaxResults(rowsCount);
			List<Object> resultList = query.list();
			session.getTransaction().commit();

			data = new String[resultList.size()][5];
			int i = 0;
			for (Object o : resultList) {
				Object[] line = (Object[]) o;
				if (line[0] != null) {
					data[i][0] = line[0].toString();
				}
				if (line[1] != null) {
					data[i][1] = line[1].toString();
				}
				if (line[2] != null) {
					data[i][2] = line[2].toString();
				}
				if (line[3] != null) {
					data[i][3] = line[3].toString();
				}
				if (line[4] != null) {
					data[i][4] = line[4].toString();
				}
				i++;
			}
		} catch (Exception e) {
			System.err.println(e);
		}

		return data;
	}

	@Override
	public int getRowsCount(DataFilter[] filters) {
		int size = 0;
		try {
			Session session = HibernateUtil.getSession();

			session.beginTransaction();
			String hql = "select p.id, p.surname, p.firstname, wc.name, wc.site from "
					+ " Person p left outer join Employmentdetails ed on p.id = ed.person_id "
					+ " left outer join Workercompany wc on ed.workercompany_id = wc.id "
					+ " where (p.display is null or p.display like '') ";
			StringBuffer where = new StringBuffer();
			if (filters != null) {
				for (DataFilter df : filters) {
					if (df != null & !df.getValue().isEmpty()) {
						where.append(" and ");
						where.append(df.getColumn());
						where.append(" like '%");
						where.append(df.getValue() + "%'");
						// crit.add(Restrictions.like(df.getColumn(), df
						// .getValue()
						// + "%"));
					}
				}
			}
			Query query = session.createSQLQuery(hql + where);
			size = query.list().size();
			session.getTransaction().commit();
		} catch (Exception e) {
			System.err.println(e);
		}
		return size;
	}

}
